﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DomainModel;  // must add this...
using System.Configuration; // must add this...
using System.Data.SqlClient; // must add this...
using System.Data; // must add this...

namespace DAL
{
    public static class DALOrders
    {
        static string connection_string = ConfigurationSettings.AppSettings["dsn"];
        /**************ORDERS PROCS*******************/
        //Insert Orders
        public static void InsertOrders(Order order, ref List<string> errors)
        {
            SqlConnection conn = new SqlConnection(connection_string);
            try
            {
                string strSQL = "insert_Orders";

                SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
                mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@customer_id", SqlDbType.Int));
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@date_created", SqlDbType.DateTime));


                //  mySA.SelectCommand.Parameters["@customer_id"].Value = customer.customer_id;
                mySA.SelectCommand.Parameters["@customer_id"].Value = order.customer_id;
                mySA.SelectCommand.Parameters["@date_created"].Value = DateTime.Now;//order.date_created;

                DataSet myDS = new DataSet();
                mySA.Fill(myDS);

            }
            catch (Exception e)
            {
                errors.Add("Error: " + e.ToString());
            }
            finally
            {
                conn.Dispose();
                conn = null;
            }
        }

        //insert_order_items
        public static void InsertOrderItems(OrderItem item, ref List<string> errors)
        {
            SqlConnection conn = new SqlConnection(connection_string);
            try
            {
                string strSQL = "insert_order_items";

                SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
                mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@order_id", SqlDbType.Int));
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@product_id", SqlDbType.Int));
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@quantity", SqlDbType.Int));
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@sub_total", SqlDbType.Decimal));
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@item_state", SqlDbType.Int));


                //  mySA.SelectCommand.Parameters["@customer_id"].Value = customer.customer_id;
                mySA.SelectCommand.Parameters["@order_id"].Value = item.order_id;
                mySA.SelectCommand.Parameters["@product_id"].Value = item.product_id;
                mySA.SelectCommand.Parameters["@quantity"].Value = item.quantity;
                mySA.SelectCommand.Parameters["@sub_total"].Value = item.sub_total;
                mySA.SelectCommand.Parameters["@item_state"].Value = item.item_state;

                DataSet myDS = new DataSet();
                mySA.Fill(myDS);

            }
            catch (Exception e)
            {
                errors.Add("Error: " + e.ToString());
            }
            finally
            {
                conn.Dispose();
                conn = null;
            }
        }

        public static List<Order> GetCustomerOrders(int id, ref List<string> errors)
        {
            SqlConnection conn = new SqlConnection(connection_string);
            Order order = null;
            List<Order> orderList = new List<Order>();
            try
            {
                string strSQL = "get_customer_orders";

                SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
                mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@customer_id", SqlDbType.Int));


                mySA.SelectCommand.Parameters["@customer_id"].Value = id;


                DataSet myDS = new DataSet();
                mySA.Fill(myDS);

                if (myDS.Tables[0].Rows.Count == 0)
                    return null;

                for (int i = 0; i < myDS.Tables[0].Rows.Count; i++)
                {
                    order = new Order();
                    order.order_id = Convert.ToInt32(myDS.Tables[0].Rows[i]["order_id"]);
                    order.total_price = Convert.ToDouble(myDS.Tables[0].Rows[i]["total_price"]);
                    order.date_created = Convert.ToDateTime(myDS.Tables[0].Rows[i]["date_created"]);
                    order.is_ordered = Convert.ToInt32(myDS.Tables[0].Rows[i]["is_ordered"]);
                    orderList.Add(order);
                }

            }
            catch (Exception e)
            {
                errors.Add("Error: " + e.ToString());
            }
            finally
            {
                conn.Dispose();
                conn = null;
            }

            return orderList;

        }

        //delete_order_item
        public static void DeleteOrderItem(int oid, int pid, ref List<string> errors)
        {
            SqlConnection conn = new SqlConnection(connection_string);
            try
            {
                string strSQL = "delete_order_item";

                SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
                mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@order_id", SqlDbType.Int));
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@product_id", SqlDbType.Int));

                mySA.SelectCommand.Parameters["@order_id"].Value = oid;
                mySA.SelectCommand.Parameters["@product_id"].Value = pid;

                DataSet myDS = new DataSet();
                mySA.Fill(myDS);

            }
            catch (Exception e)
            {
                errors.Add("Error: " + e.ToString());
            }
            finally
            {
                conn.Dispose();
                conn = null;
            }
        }

        //get_order_products
        public static List<OrderItem> GetOrderProducts(int id, ref List<string> errors)
        {
            SqlConnection conn = new SqlConnection(connection_string);
            OrderItem item = null;
            List<OrderItem> itemList = new List<OrderItem>();
            try
            {
                string strSQL = "get_order_products";

                SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
                mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@order_id", SqlDbType.Int));
              

                mySA.SelectCommand.Parameters["@order_id"].Value = id;


                DataSet myDS = new DataSet();
                mySA.Fill(myDS);

                if (myDS.Tables[0].Rows.Count == 0)
                    return null;

                for (int i = 0; i < myDS.Tables[0].Rows.Count; i++)
                {
                    item = new OrderItem();
                    item.order_id = id;
                    item.product_id = (int)myDS.Tables[0].Rows[i]["product_id"];
                    item.quantity = (int)myDS.Tables[0].Rows[i]["quantity"];
                    item.sub_total = Convert.ToDouble(myDS.Tables[0].Rows[i]["sub_total"]);
                    itemList.Add(item);
                }

            }
            catch (Exception e)
            {
                errors.Add("Error: " + e.ToString());
            }
            finally
            {
                conn.Dispose();
                conn = null;
            }

            return itemList;
        }


        //get_order_totalprice
        public static double GetOrderTotalPrice(int id, ref List<string> errors)
        {
            SqlConnection conn = new SqlConnection(connection_string);
            Double price = 0;
            List<Order> itemList = new List<Order>();
            try
            {
                string strSQL = "get_order_totalprice";

                SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
                mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@order_id", SqlDbType.Int));

                mySA.SelectCommand.Parameters["@order_id"].Value = id;

                DataSet myDS = new DataSet();
                mySA.Fill(myDS);

                price = Convert.ToDouble(myDS.Tables[0].Rows[0]["total_price"]);

            }
            catch (Exception e)
            {
                errors.Add("Error: " + e.ToString());
            }
            finally
            {
                conn.Dispose();
                conn = null;
            }

            return price;
        }

        public static void SetOrderTotalPrice(int id, ref List<string> errors)
        {
            SqlConnection conn = new SqlConnection(connection_string);
           
            List<Order> itemList = new List<Order>();
            try
            {
                string strSQL = "set_total_price";

                SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
                mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@order_id", SqlDbType.Int));

                mySA.SelectCommand.Parameters["@order_id"].Value = id;

                DataSet myDS = new DataSet();
                mySA.Fill(myDS);

             //   price = Convert.ToDouble(myDS.Tables[0].Rows[0]["total_price"]);

            }
            catch (Exception e)
            {
                errors.Add("Error: " + e.ToString());
            }
            finally
            {
                conn.Dispose();
                conn = null;
            }

          //  return price;
        }

        //This procedure deletes all records
        public static void DeleteAllRecords(ref List<string> errors)
        {
            SqlConnection conn = new SqlConnection(connection_string);

            try
            {
                string strSQL = "delete_all_records";

                SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
                mySA.SelectCommand.CommandType = CommandType.StoredProcedure;

                DataSet myDS = new DataSet();
                mySA.Fill(myDS);

            }
            catch (Exception e)
            {
                errors.Add("Error: " + e.ToString());
            }
            finally
            {
                conn.Dispose();
                conn = null;
            }
        }

        //numOrderItemEntries
        public static int numOrderEntries(ref List<string> errors)
        {
            SqlConnection conn = new SqlConnection(connection_string);
            int num = 0;
            try
            {
                string strSQL = "numOrderEntries";

                SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
                mySA.SelectCommand.CommandType = CommandType.StoredProcedure;

                DataSet myDS = new DataSet();
                mySA.Fill(myDS);

                num = Convert.ToInt32(myDS.Tables[0].Rows[0]["numorders"]);

            }
            catch (Exception e)
            {
                errors.Add("Error: " + e.ToString());
            }
            finally
            {
                conn.Dispose();
                conn = null;
            }

            return num;
        }

        //numOrderItemEntries
        public static int numOrderItemEntries(ref List<string> errors)
        {
            SqlConnection conn = new SqlConnection(connection_string);
            int num = 0;
            try
            {
                string strSQL = "numOrderItemEntries";

                SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
                mySA.SelectCommand.CommandType = CommandType.StoredProcedure;

                DataSet myDS = new DataSet();
                mySA.Fill(myDS);

                num = Convert.ToInt32(myDS.Tables[0].Rows[0]["numitems"]);

            }
            catch (Exception e)
            {
                errors.Add("Error: " + e.ToString());
            }
            finally
            {
                conn.Dispose();
                conn = null;
            }
            return num;
        }

        //this procedure places an order for the customer
        public static void PlaceOrder(int order_num, ref List<string> errors)
        {
            SqlConnection conn = new SqlConnection(connection_string);
            try
            {
                string strSQL = "place_Order";

                SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
                mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@order_id", SqlDbType.Int));
                mySA.SelectCommand.Parameters["@order_id"].Value = order_num;

                DataSet myDS = new DataSet();
                mySA.Fill(myDS);

            }
            catch (Exception e)
            {
                errors.Add("Error: " + e.ToString());
            }
            finally
            {
                conn.Dispose();
                conn = null;
            }
        }

        //this procedure checks if a ordered is placed
        public static int SeeIfOrderPlaced(int order_num, ref List<string> errors)
        {
            SqlConnection conn = new SqlConnection(connection_string);
            int placed = -1;
            try
            {
                string strSQL = "isOrderPlaced";

                SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
                mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@order_id", SqlDbType.Int));
                mySA.SelectCommand.Parameters["@order_id"].Value = order_num;

                DataSet myDS = new DataSet();
                mySA.Fill(myDS);

                placed = Convert.ToInt32(myDS.Tables[0].Rows[0]["is_ordered"].ToString());
            }
            catch (Exception e)
            {
                errors.Add("Error: " + e.ToString());
            }
            finally
            {
                conn.Dispose();
                conn = null;
            }
            return placed;
        }

        

        //this procedure returns the items in the customer's cart
        /*public static List<Product> SeeMyCart(int identity, ref List<string> errors)
        {
            SqlConnection conn = new SqlConnection(connection_string);
            List<Order> cart_items = new List<Product>();
            try
            {
                string strSQL = "see_my_cart";

                SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
                mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@who_am_i", SqlDbType.Int));
                mySA.SelectCommand.Parameters["@who_am_i"].Value = identity;

                DataSet myDS = new DataSet();
                mySA.Fill(myDS);

                int id = 0;
                int siz = 0;
                for (int i = 0; i < myDS.Tables[0].Rows.Count; i++)
                {
                    id = Convert.ToInt32(myDS.Tables[0].Rows[i]["size_id"].ToString());
                    siz = Convert.ToInt32(myDS.Tables[0].Rows[i]["size"].ToString());
                    all_sizes.Add(id, siz);
                }
        

            return cart_items;
        }*/

        //see_my_history
        public static List<OrderItem> SeeMyHistory(int id, ref List<string> errors)
        {
            SqlConnection conn = new SqlConnection(connection_string);
            OrderItem item = null;
            List<OrderItem> itemList = new List<OrderItem>();
            try
            {
                string strSQL = "see_my_history";

                SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
                mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@who_am_i", SqlDbType.Int));

                mySA.SelectCommand.Parameters["@who_am_i"].Value = id;


                DataSet myDS = new DataSet();
                mySA.Fill(myDS);

                if (myDS.Tables[0].Rows.Count == 0)
                    return null;

                for (int i = 0; i < myDS.Tables[0].Rows.Count; i++)
                {
                    item = new OrderItem();
                    item.product_id = (int)myDS.Tables[0].Rows[0]["product_id"];
                    item.quantity = (int)myDS.Tables[0].Rows[0]["quantity"];
                    item.sub_total = Convert.ToDouble(myDS.Tables[0].Rows[0]["sub_total"]);
                    itemList.Add(item);
                }

            }
            catch (Exception e)
            {
                errors.Add("Error: " + e.ToString());
            }
            finally
            {
                conn.Dispose();
                conn = null;
            }

            return itemList;
        }

        /*
 * GetId : Helper method to search order id
 * 
 */

        public static Order getOrderId(Order orderid, ref List<string> errors)
        {

            SqlConnection conn = new SqlConnection(connection_string);
            Order orderfuckernumber = new Order();

            try
            {
                string strSQL = "search_order_id";

                SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
                mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@DAYS", SqlDbType.DateTime, 50));

                mySA.SelectCommand.Parameters["@DAYS"].Value = orderid.date_created;
              

                DataSet myDS = new DataSet();
                mySA.Fill(myDS);


                orderfuckernumber.order_id = Convert.ToInt16(myDS.Tables[0].Rows[0]["order_id"]);

            }
            catch (Exception e)
            {
                errors.Add("Error: " + e.ToString());
            }
            finally
            {
                conn.Dispose();
                conn = null;
            }



            return orderfuckernumber;


        }


    }
}